package com.telecom.sxint.app.api.controller.SystemController.App1SystemFileController;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.telecom.sxint.app.core.domain.entity.Systempojo.App1Monthlypojo.App1Assessment;
import com.telecom.sxint.app.core.domain.entity.Systempojo.App1YunwangWorkpojo.App1YunwangPublicSheet;
import com.telecom.sxint.app.core.domain.entity.Systempojo.App1YunwangWorkpojo.App1YunwangWorkEvaluationIndicators;
import com.telecom.sxint.app.core.domain.entity.Systempojo.App1YunwangWorkpojo.App1YunwangWorkEvaluationIndicatorsData;
import com.telecom.sxint.app.core.domain.entity.Systempojo.App1countyIndicatorspojo.App1CountyIndicators;
import com.telecom.sxint.app.core.domain.entity.Systempojo.App1countyIndicatorspojo.App1QuarterPublicSheet;
import com.telecom.sxint.app.core.domain.entity.dto.AnalyseCountyDTO;
import com.telecom.sxint.app.core.domain.entity.dto.EvaluationIndicatorsDTO;
import com.telecom.sxint.app.core.service.Systemservice.App1Monthlyservice.IApp1AssessmentService;
import com.telecom.sxint.app.core.service.Systemservice.App1YunwangWorkservice.IApp1YunwangPublicSheetService;
import com.telecom.sxint.app.core.service.Systemservice.App1YunwangWorkservice.IApp1YunwangWorkEvaluationIndicatorsDataService;
import com.telecom.sxint.app.core.service.Systemservice.App1YunwangWorkservice.IApp1YunwangWorkEvaluationIndicatorsService;
import com.telecom.sxint.app.core.service.Systemservice.App1YunwangWorkservice.IApp1YunwangWorkOperationSummaryService;
import com.telecom.sxint.app.core.service.Systemservice.App1countyIndicatorsservice.IApp1CountyIndicatorsService;
import com.telecom.sxint.app.core.service.Systemservice.App1countyIndicatorsservice.IApp1QuarterPublicSheetService;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

/**
 * @author zhulintao
 * @version 2.0
 * @date 2024/8/27 15:28
 */
@RestController
@Tag(name = "导入导出控制器", description = "导入导出控制器")
@RequestMapping("/app1-system")
public class App1SystemFileExport {
    @Autowired
    private IApp1YunwangWorkEvaluationIndicatorsService iApp1YunwangWorkEvaluationIndicatorsService;
    @Autowired
    private IApp1YunwangWorkEvaluationIndicatorsDataService iApp1YunwangWorkEvaluationIndicatorsDataService;
    @Autowired
    private IApp1AssessmentService iApp1AssessmentService;
    @Autowired
    private IApp1YunwangPublicSheetService iApp1YunwangPublicSheetService;
    @Autowired
    private IApp1QuarterPublicSheetService iApp1QuarterPublicSheetService;
    @Autowired
    private IApp1YunwangWorkOperationSummaryService iApp1YunwangWorkOperationSummaryService;
    @Autowired
    private IApp1CountyIndicatorsService iApp1CountyIndicatorsService;
    /**
     * 生成excel文件下载，一个sheet
     * @param
     * @return
     */
    public static void downloadUnfilledToSheet(String excelName,String sheetName,List<List<String>>headers,List<Integer> mergeColumeIndex,int mergeRowIndex, HttpServletResponse response, Class cla, List list) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode(excelName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*="+fileName+ ".xlsx");
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteFont.setBold(true);
        headWriteCellStyle.setFillForegroundColor((short) 44);
        //内容样式策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        // 从第二行后开始合并
        EasyExcel.write(response.getOutputStream(), cla)
                .head(headers)
                .registerWriteHandler(new ExcelMergeUtil(mergeRowIndex, mergeColumeIndex))
                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                .autoCloseStream(Boolean.TRUE)
                .sheet(sheetName)
                .doWrite(list);
    }

    /**
     * 生成excel文件下载，多个sheet
     * @param
     * @return
     */
    public static void downloadUnfilledToSheets(String excelName, List<String> sheetNames, List<Integer> mergeColumeIndex, int mergeRowIndex, HttpServletResponse response, Class cla, List<List<App1Assessment>> lists) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode(excelName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*="+fileName+ ".xlsx");
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteFont.setBold(true);
        headWriteCellStyle.setFillForegroundColor((short) 44);
        //内容样式策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                .registerWriteHandler(new ExcelMergeUtil(mergeRowIndex,mergeColumeIndex))
                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                .build();
        WriteSheet writeSheet =new WriteSheet();
        for(int i=0;i<lists.size();i++){
            lists.get(i).removeIf(app1Assessment -> StrUtil.isBlank(app1Assessment.getAssessmentItem())
                    &&StrUtil.isBlank(app1Assessment.getCategory())
                    &&StrUtil.isBlank(app1Assessment.getNotes())
                    &&StrUtil.isBlank(app1Assessment.getScore())
                    &&StrUtil.isBlank(app1Assessment.getWeight())
                    &&StrUtil.isBlank(app1Assessment.getCompletionValue())
                    &&StrUtil.isBlank(app1Assessment.getSelfScoringMethod())
                    &&StrUtil.isBlank(app1Assessment.getTargetValue())
                    &&StrUtil.isBlank(app1Assessment.getCompletionRateRank())
                    &&StrUtil.isBlank(app1Assessment.getTarget()));
            writeSheet = EasyExcel.writerSheet(i,sheetNames.get(i)).head(getAssHeader(sheetNames.get(i))).build();
            excelWriter.write(lists.get(i),writeSheet);
        }
        excelWriter.finish();
    }
    /**
     * 返回表头
     *
     * 外层List代表行内层 List代表列  相同的列数据会被主动合并
     * 构造双列表头
     *                合并列1             合并列2
     *  机构名称   合11   合12   合13    合21  合22  合23  合24    合计
     *
     * @return List<List<String>>
     */
    private static List<List<String>> getHeader(){
        List<List<String>> line = new ArrayList<>();
        List<String> column = new ArrayList<>();
        column.add("2024年区县云网工作评价指标体系");
        column.add("考核项目");
        line.add(column);
        column = new ArrayList<>();
        column.add("2024年区县云网工作评价指标体系");
        column.add("大指标");
        line.add(column);
        column = new ArrayList<>();
        column.add("2024年区县云网工作评价指标体系");
        column.add("大指标分值");
        line.add(column);
//        开始构造合并列2
        column = new ArrayList<>();
        column.add("2024年区县云网工作评价指标体系");
        column.add("考核数据的提供部门和取数的系统");
        line.add(column);
        column = new ArrayList<>();
        column.add("2024年区县云网工作评价指标体系");
        column.add("考核周期");
        line.add(column);
        column = new ArrayList<>();
        column.add("2024年区县云网工作评价指标体系");
        column.add("目标值");
        line.add(column);
        column = new ArrayList<>();
        column.add("2024年区县云网工作评价指标体系");
        column.add("指标定义（解释）");
        line.add(column);
        column = new ArrayList<>();
        column.add("2024年区县云网工作评价指标体系");
        column.add("扣加分方法");
        line.add(column);

        column = new ArrayList<>();
        column.add("2024年7月数据");
        column.add("越城");
        line.add(column);
        column = new ArrayList<>();
        column.add("2024年7月数据");
        column.add("柯桥");
        line.add(column);
        column = new ArrayList<>();
        column.add("2024年7月数据");
        column.add("上虞");
        line.add(column);
        column = new ArrayList<>();
        column.add("2024年7月数据");
        column.add("嵊州");
        line.add(column);
        column = new ArrayList<>();
        column.add("2024年7月数据");
        column.add("新昌");
        line.add(column);
        column = new ArrayList<>();
        column.add("2024年7月数据");
        column.add("诸暨");
        line.add(column);
//        构造双层标题完成返回
        return line;
    }
    /**
     * 返回季度的表头
     *
     */
    private List<List<String>> getQuarterHeader(String sheetName){
        List<App1QuarterPublicSheet> quarterHeaders=iApp1QuarterPublicSheetService.selectFieldBySheetName(sheetName);
        int lens=quarterHeaders.size();
        List<List<String>> line = new ArrayList<>();
        List<String> column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldA());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldB());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldC());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldD());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldE());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldF());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldG());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldH());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldI());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldJ());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldK());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldL());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldM());
        }
        line.add(column);column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldN());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(quarterHeaders.get(i).getGeneralFieldO());
        }
        line.add(column);

        return line;
    }

    /**
     * 返回云网运营部汇总的表头
     * @return
     */
    private List<List<String>> getOperationSummaryHeader(){
        List<List<String>> line = new ArrayList<>();
        List<String> column = new ArrayList<>();
        List<String> items=iApp1YunwangWorkOperationSummaryService.getAssessmentItem();
        column.add("区县公司");
        line.add(column);
        for(int i=0;i<items.size();i++){
            column = new ArrayList<>();
            column.add("考核得分");
            column.add(items.get(i));
            line.add(column);
        }
        column = new ArrayList<>();
        column.add("小计");
        line.add(column);
        return line;
    }
    /**
     * 返回表头
     *
     */
    private List<List<String>> getYunwangHeader(String sheetName){
        List<App1YunwangPublicSheet> yunwangHeaders=iApp1YunwangPublicSheetService.selectFieldBySheetName(sheetName);
        int lens=yunwangHeaders.size();
        List<List<String>> line = new ArrayList<>();
        List<String> column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldA());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldB());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldC());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldD());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldE());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldF());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldG());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldH());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldI());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldJ());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldK());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldL());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldM());
        }
        line.add(column);column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldN());
        }
        line.add(column);
        column = new ArrayList<>();
        for(int i=0;i<lens;i++){
            column.add(yunwangHeaders.get(i).getGeneralFieldO());
        }
        line.add(column);

        return line;
    }
    /**
     * 合并excel文档导出
     * @param response
     * @return
     */
    @Operation(summary = "云网excel文档导出", description = "返回测试信息表分页")
    @GetMapping("exportAllIndicatorsAndData")
    public void exportAll(HttpServletResponse response) throws IOException {
        List<EvaluationIndicatorsDTO> dtoList = iApp1YunwangWorkEvaluationIndicatorsService.selectWithData();
        List<Integer> mergeColumeIndex = new ArrayList<>();
        List<List<String>> headers = getHeader();
        // 设置响应头
        mergeColumeIndex.add(0);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = java.net.URLEncoder.encode("云网工作评价指标体系", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");
        ExcelWriter excelWriter = null;
        //一些样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteFont.setBold(true);
        headWriteCellStyle.setFillForegroundColor((short) 44);
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        try {
            excelWriter = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet writeSheet = EasyExcel.writerSheet("网运")
                    .head(headers)
                    .registerWriteHandler(new ExcelMergeUtil(2,mergeColumeIndex))
                    .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                    .build();
            //汇总表的数据
            List<EvaluationIndicatorsDTO> data = dtoList;
            excelWriter.write(data, writeSheet);
            List<String> sheetNames = iApp1YunwangPublicSheetService.selectAllSheetNames();
            List<List<App1YunwangPublicSheet>> yunwangSheets = new ArrayList<>();
            for(int i=0;i<sheetNames.size();i++){
                List<App1YunwangPublicSheet> list1 = iApp1YunwangPublicSheetService.selectDataBySheetName(sheetNames.get(i));
                yunwangSheets.add(list1);
            }

            for (int i = 0; i < yunwangSheets.size(); i++) {
                List<List<String>> lis = getYunwangHeader(sheetNames.get(i));
                int count=0;
                for(int j=lis.size();j>=0;j--){
                    if(areAllElementsEmpty(lis.get(j-1))) {
                        count++;
                    }else {
                        break;
                    }
                }
                writeSheet =EasyExcel.writerSheet(sheetNames.get(i))//sheet名称
                        .head(getYunwangHeader(sheetNames.get(i)))//表头
                        .registerWriteHandler(new ExcelMerge())//合并单元格
                        .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))//样式
                        .build();
                excelWriter.write(yunwangSheets.get(i), writeSheet);
            }

        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * 区县数据excel文档导出
     * @param response
     * @return
     */
    @Operation(summary = "区县数据excel文档导出", description = "返回测试信息表分页")
    @GetMapping("exportAllApp1YunwangWorkEvaluationIndicatorsData")
    public void exportAllData(HttpServletResponse response) throws IOException {
        List<App1YunwangWorkEvaluationIndicatorsData> list = iApp1YunwangWorkEvaluationIndicatorsDataService.getAllByAsc();
        List<Integer> mergeColumeIndex = new ArrayList<>();
        mergeColumeIndex.add(0);
        List<List<String>> headers = new ArrayList<>();
        downloadUnfilledToSheet("云网工作评价指标体系","区县数据",headers,mergeColumeIndex,2, response, App1YunwangWorkEvaluationIndicatorsData.class, list);
    }

    /**
     * 返回表头
     */
    private static List<List<String>> getAssHeader(String department){
        List<List<String>> line = new ArrayList<>();
        List<String> column = new ArrayList<>();
        column.add("被考核部门："+department);
        column.add("类别");
        line.add(column);
        column = new ArrayList<>();
        column.add("被考核部门："+department);
        column.add("考核项目");
        line.add(column);
        column = new ArrayList<>();
        column.add("被考核部门："+department);
        column.add("权重");
        line.add(column);
        column = new ArrayList<>();
        column.add("被考核部门："+department);
        column.add("目标");
        line.add(column);
        column = new ArrayList<>();
        column.add("被考核部门："+department);
        column.add("自评分计分方法");
        line.add(column);
        column = new ArrayList<>();
        column.add(" ");
        column.add("备注");
        line.add(column);
        column = new ArrayList<>();
        column.add(" ");
        column.add("目标值");
        line.add(column);
        column = new ArrayList<>();
        column.add(" ");
        column.add("完成值");
        line.add(column);
        column = new ArrayList<>();
        column.add(" ");
        column.add("完成率/排名|全省排名");
        line.add(column);
        column = new ArrayList<>();
        column.add(" ");
        column.add("得分");
        line.add(column);
        return line;
    }

    /**
     * 考核excel文档导出,一个sheet
     * @param response
     * @return
     */
    @Operation(summary = "单个部门考核excel文档导出", description = "返回测试信息表分页")
    @GetMapping("exportAssessment/{department}")
    public void exportAssessment(HttpServletResponse response,@PathVariable String department) throws IOException {
        List<App1Assessment> list=iApp1AssessmentService.selectWithDepartment(department);
        list.removeIf(app1Assessment -> StrUtil.isBlank(app1Assessment.getAssessmentItem())
                &&StrUtil.isBlank(app1Assessment.getCategory())
                &&StrUtil.isBlank(app1Assessment.getNotes())
                &&StrUtil.isBlank(app1Assessment.getScore())
                &&StrUtil.isBlank(app1Assessment.getWeight())
                &&StrUtil.isBlank(app1Assessment.getCompletionValue())
                &&StrUtil.isBlank(app1Assessment.getSelfScoringMethod())
                &&StrUtil.isBlank(app1Assessment.getTargetValue())
                &&StrUtil.isBlank(app1Assessment.getCompletionRateRank())
                &&StrUtil.isBlank(app1Assessment.getTarget()));
        List<Integer> mergeColumeIndex = new ArrayList<>();
        //需要合并的列的编号
        mergeColumeIndex.add(0);
        mergeColumeIndex.add(1);
        mergeColumeIndex.add(3);
        mergeColumeIndex.add(4);
        downloadUnfilledToSheet("考核",department,getAssHeader(department),mergeColumeIndex,2, response, App1Assessment.class, list);
    }

    /**
     * 考核excel文档导出,多个sheet
     * @param response
     * @return
     */
    @Operation(summary = "全部部门考核excel文档导出", description = "返回测试信息表分页")
    @GetMapping("exportAllAssessment")
    public void exportAllAssessment(HttpServletResponse response) throws IOException {
        List<List<App1Assessment>> AssSheets = new ArrayList<>();
        List<String> departments = iApp1AssessmentService.selectAllDepartment();
        for (String department : departments) {
            List<App1Assessment> list1 = iApp1AssessmentService.selectWithDepartment(department);
            AssSheets.add(list1);
        }
        List<Integer> mergeColumeIndex = new ArrayList<>();
        //需要合并的列的编号
        mergeColumeIndex.add(0);
        mergeColumeIndex.add(1);
        mergeColumeIndex.add(3);
        mergeColumeIndex.add(4);
        downloadUnfilledToSheets("考核",departments,mergeColumeIndex,2, response, App1Assessment.class, AssSheets);
    }
    /**
     * 返回县分表头
     */
    private static List<List<String>> getCountyHeader(){
        List<List<String>> line = new ArrayList<>();
        List<String> column = new ArrayList<>();
        column.add("附件");
        column.add("");
        line.add(column);
        column = new ArrayList<>();
        column.add("区县网运线季度考核指标");
        column.add("指标名称");
        line.add(column);
        column = new ArrayList<>();
        column.add("区县网运线季度考核指标");
        column.add("分值");
        line.add(column);
        column = new ArrayList<>();
        column.add("区县网运线季度考核指标");
        column.add("具体考核办法");
        line.add(column);
        column = new ArrayList<>();
        column.add("");
        column.add("提供人");
        line.add(column);
        return line;
    }

    /**
     * 返回云网数据分析表头
     */
    private static List<List<String>> getAnalyseYunwangHeader(){
        List<List<String>> line = new ArrayList<>();
        List<String> column = new ArrayList<>();
        column.add("模块");
        line.add(column);
        column = new ArrayList<>();
        column.add("越城");
        line.add(column);
        column = new ArrayList<>();
        column.add("柯桥");
        line.add(column);
        column = new ArrayList<>();
        column.add("上虞");
        line.add(column);
        column = new ArrayList<>();
        column.add("嵊州");
        line.add(column);
        column = new ArrayList<>();
        column.add("新昌");
        line.add(column);
        column = new ArrayList<>();
        column.add("诸暨");
        line.add(column);
        column = new ArrayList<>();
        column.add("分值");
        line.add(column);
        return line;
    }

    /**
     * 季度excel文档导出
     * @param response
     * @return
     */
    @Operation(summary = "季度excel文档导出", description = "返回测试信息表分页")
    @GetMapping("exportAllQuarterData")
    public void exportAllQuarterData(HttpServletResponse response) throws IOException {
        List<Integer> mergeColumeIndex = new ArrayList<>();
        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = java.net.URLEncoder.encode("季度", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
        ExcelWriter excelWriter = null;
        //一些样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setFillForegroundColor((short) 44);
        headWriteFont.setBold(true);
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        try {
            // 创建查询包装器并添加 is_delete = 0 的条件
            QueryWrapper<App1CountyIndicators> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("is_delete", 0);
            List<App1CountyIndicators> countys = iApp1CountyIndicatorsService.list(queryWrapper);
            excelWriter = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet writeSheet =EasyExcel.writerSheet("县分指标")
                    .head(App1CountyIndicators.class)
                    .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                    .build();
            excelWriter.write(countys, writeSheet);
            List<String> sheetNames = iApp1QuarterPublicSheetService.selectAllSheetNames();
            List<List<App1QuarterPublicSheet>> quarterSheets = new ArrayList<>();
            for(int i=0;i<sheetNames.size();i++){
                List<App1QuarterPublicSheet> list1 = iApp1QuarterPublicSheetService.selectDataBySheetName(sheetNames.get(i));
                quarterSheets.add(list1);
            }
            for (int i = 0; i < quarterSheets.size(); i++) {
                writeSheet = EasyExcel.writerSheet(sheetNames.get(i))//sheet名称
                        .head(getQuarterHeader(sheetNames.get(i)))//表头
                        .registerWriteHandler(new ExcelMerge())//合并单元格
                        .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))//样式
                        .build();
                excelWriter.write(quarterSheets.get(i), writeSheet);
            }

        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * 云网数据分析文档导出
     * @param response
     * @return
     */
    @Operation(summary = "云网数据分析文档导出", description = "返回测试信息表分页")
    @GetMapping("exportAnalyseYunwangData")
    public void exportAnalyseYunwangData(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = java.net.URLEncoder.encode("图表数据", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
        ExcelWriter excelWriter = null;

        List<App1YunwangWorkEvaluationIndicatorsData> everyCountyData = iApp1YunwangWorkEvaluationIndicatorsDataService.list();
        List<String> AllEvaluationItems = iApp1YunwangWorkEvaluationIndicatorsDataService.selectAllevaluationItem();
        List<App1YunwangWorkEvaluationIndicatorsData> countys = new ArrayList<>();
        List<App1YunwangWorkEvaluationIndicators> forScore = new ArrayList<>();
        List<AnalyseCountyDTO> datas = new ArrayList<>();
        AnalyseCountyDTO header = new AnalyseCountyDTO();
        AnalyseCountyDTO ender = new AnalyseCountyDTO();
        header.setEvaluationItem("模块");
        header.setYuecheng("越城");
        header.setKeqiao("柯桥");
        header.setShangyu("上虞");
        header.setShengzhou("嵊州");
        header.setXinchang("新昌");
        header.setZhuji("诸暨");
        header.setScore("分值");
        datas.add(header);
        ender.setEvaluationItem("综合评价");
        for(int i=0;i<AllEvaluationItems.size();i++){
            AnalyseCountyDTO analyseCountyDTO = new AnalyseCountyDTO();
            analyseCountyDTO.setEvaluationItem(ThinAssessedItem(AllEvaluationItems.get(i)));
            countys=iApp1YunwangWorkEvaluationIndicatorsDataService.selectByevaluationItem(AllEvaluationItems.get(i));
            forScore = iApp1YunwangWorkEvaluationIndicatorsService.selectByevaluationItem(AllEvaluationItems.get(i));
            BigDecimal sum1 = BigDecimal.ZERO;
            BigDecimal sum2 = BigDecimal.ZERO;
            BigDecimal sum3 = BigDecimal.ZERO;
            BigDecimal sum4 = BigDecimal.ZERO;
            BigDecimal sum5 = BigDecimal.ZERO;
            BigDecimal sum6 = BigDecimal.ZERO;
            //计算每个指标的分值
            for(int j=0;j<countys.size();j++){
                if(isNumeric(countys.get(j).getYuecheng())){
                    sum1 = sum1.add(new BigDecimal(countys.get(j).getYuecheng()));
                }else {
                    sum1 = sum1.add(BigDecimal.ZERO);
                }
                if(isNumeric(countys.get(j).getKeqiao())){
                    sum2 = sum2.add(new BigDecimal(countys.get(j).getKeqiao()));
                }else {
                    sum2 = sum2.add(BigDecimal.ZERO);
                }
                if(isNumeric(countys.get(j).getShangyu())){
                    sum3 = sum3.add(new BigDecimal(countys.get(j).getShangyu()));
                }else {
                    sum3 = sum3.add(BigDecimal.ZERO);
                }
                if(isNumeric(countys.get(j).getShengzhou())){
                    sum4 = sum4.add(new BigDecimal(countys.get(j).getShengzhou()));
                }else {
                    sum4 = sum4.add(BigDecimal.ZERO);
                }
                if(isNumeric(countys.get(j).getXinchang())){
                    sum5 = sum5.add(new BigDecimal(countys.get(j).getXinchang()));
                }else {
                    sum5 = sum5.add(BigDecimal.ZERO);
                }
                if(isNumeric(countys.get(j).getZhuji())){
                    sum6 = sum6.add(new BigDecimal(countys.get(j).getZhuji()));
                }else {
                    sum6 = sum6.add(BigDecimal.ZERO);
                }
            }
            //设置每个指标的分值
            analyseCountyDTO.setYuecheng(String.valueOf(sum1));
            analyseCountyDTO.setKeqiao(String.valueOf(sum2));
            analyseCountyDTO.setShangyu(String.valueOf(sum3));
            analyseCountyDTO.setShengzhou(String.valueOf(sum4));
            analyseCountyDTO.setXinchang(String.valueOf(sum5));
            analyseCountyDTO.setZhuji(String.valueOf(sum6));
            BigDecimal sum = BigDecimal.ZERO;
            //从表中获取每个指标的分值
            for(int j=0;j<forScore.size();j++) {
                if (isNumeric(forScore.get(j).getMainIndicatorScore())) {
                    sum = sum.add(new BigDecimal(forScore.get(j).getMainIndicatorScore()));
                } else {
                    sum = sum.add(BigDecimal.ZERO);
                }
                analyseCountyDTO.setScore(String.valueOf(sum));
            }
            datas.add(analyseCountyDTO);
        }
        BigDecimal sum1 = BigDecimal.ZERO;
        BigDecimal sum2 = BigDecimal.ZERO;
        BigDecimal sum3 = BigDecimal.ZERO;
        BigDecimal sum4 = BigDecimal.ZERO;
        BigDecimal sum5 = BigDecimal.ZERO;
        BigDecimal sum6 = BigDecimal.ZERO;
        BigDecimal sumAll = BigDecimal.ZERO;
        for(int i=0;i<datas.size();i++){
            if(isNumeric(datas.get(i).getYuecheng())){
                sum1= sum1.add(new BigDecimal(datas.get(i).getYuecheng()));
            }else {
                sum1 = sum1.add(BigDecimal.ZERO);
            }
            if(isNumeric(datas.get(i).getKeqiao())){
                sum2= sum2.add(new BigDecimal(datas.get(i).getKeqiao()));
            }else {
                sum2 = sum2.add(BigDecimal.ZERO);
            }
            if(isNumeric(datas.get(i).getShangyu())){
                sum3= sum3.add(new BigDecimal(datas.get(i).getShangyu()));
            }else {
                sum3 = sum3.add(BigDecimal.ZERO);
            }
            if(isNumeric(datas.get(i).getShengzhou())){
                sum4= sum4.add(new BigDecimal(datas.get(i).getShengzhou()));
            }else {
                sum4 = sum4.add(BigDecimal.ZERO);
            }
            if(isNumeric(datas.get(i).getXinchang())){
                sum5= sum5.add(new BigDecimal(datas.get(i).getXinchang()));
            }else {
                sum5 = sum5.add(BigDecimal.ZERO);
            }
            if(isNumeric(datas.get(i).getZhuji())){
                sum6= sum6.add(new BigDecimal(datas.get(i).getZhuji()));
            }else {
                sum6 = sum6.add(BigDecimal.ZERO);
            }
            if(isNumeric(datas.get(i).getScore())){
                sumAll= sumAll.add(new BigDecimal(datas.get(i).getScore()));
            }else {
                sumAll = sumAll.add(BigDecimal.ZERO);
            }
        }
        ender.setYuecheng(String.valueOf(sum1));
        ender.setKeqiao(String.valueOf(sum2));
        ender.setShangyu(String.valueOf(sum3));
        ender.setShengzhou(String.valueOf(sum4));
        ender.setXinchang(String.valueOf(sum5));
        ender.setZhuji(String.valueOf(sum6));
        ender.setScore(String.valueOf(sumAll));
        datas.add(ender);
        List<List<String>> models=new ArrayList<>();
        List<String> model = new ArrayList<>();
        model.add("模块");
        for(int i=0;i<AllEvaluationItems.size();i++){
            //优化
            model.add(ThinAssessedItem(AllEvaluationItems.get(i)));
        }
        model.add("合计");
        models.add(model);

        List<String> modelA = new ArrayList<>();
        modelA.add("越城");
        for(int i=1;i<datas.size();){
            modelA.add(datas.get(i++).getYuecheng());
        }
        models.add(modelA);
        List<String> modelB = new ArrayList<>();
        modelB.add("柯桥");
        for(int i=1;i<datas.size();){
            modelB.add(datas.get(i++).getKeqiao());
        }
        models.add(modelB);
        List<String> modelC = new ArrayList<>();
        modelC.add("上虞");
        for(int i=1;i<datas.size();){
            modelC.add(datas.get(i++).getShangyu());
        }
        models.add(modelC);
        List<String> modelD = new ArrayList<>();
        modelD.add("嵊州");
        for(int i=1;i<datas.size();){
            modelD.add(datas.get(i++).getShengzhou());
        }
        models.add(modelD);
        List<String> modelE = new ArrayList<>();
        modelE.add("新昌");
        for(int i=1;i<datas.size();){
            modelE.add(datas.get(i++).getXinchang());
        }
        models.add(modelE);
        List<String> modelF = new ArrayList<>();
        modelF.add("诸暨");
        for(int i=1;i<datas.size();){
            modelF.add(datas.get(i++).getZhuji());
        }
        models.add(modelF);


        List<String> lists=new ArrayList<>();
        lists.add("");
        try{
            excelWriter = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet writeSheet =EasyExcel.writerSheet("图表数据")
                    .build();
            excelWriter.write(datas, writeSheet);
            excelWriter.write(lists, writeSheet);
            excelWriter.write(lists, writeSheet);
            excelWriter.write(models, writeSheet);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * 判断字符串是否为数字
     * @return
     */
    private boolean isNumeric(String str) {
        try {
            Double.parseDouble(str);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }

    /**
     * 优化指标名称
     * @return
     */
    private String ThinAssessedItem(String str){
        if(str.contains("（")){
            return str.substring(str.indexOf("、")+1,str.indexOf("（"));
        }else {
            return str;
        }
    }

    public static boolean areAllElementsEmpty(List<?> list) {
        if (list == null) {
            throw new IllegalArgumentException("The list must not be null.");
        }

        for (Object element : list) {
            if (element == null) {
                continue; // null is considered empty
            }
            if (!(element instanceof String) || !((String) element).isEmpty()) {
                return false; // non-empty element found
            }
        }
        return true; // all elements are empty
    }
}
